-- =============================================
-- add address
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_Address_Create' 
)
   DROP PROCEDURE ECommerce_Address_Create
GO

CREATE PROCEDURE ECommerce_Address_Create
	@UserID uniqueidentifier,
	@IsBillingAddress bit,
	@FirstName nvarchar(100),
	@LastName nvarchar(100),
	@PhoneNumber nvarchar(50),
	@Email nvarchar(255),
	@FaxNumber nvarchar(50),
	@Company nvarchar(100),
	@Address1 nvarchar(100),
	@Address2 nvarchar(100),
	@CityID int,
	@StateProvinceID int,
	@ZipPostalCode nvarchar(10),
	@CountryID int,
	@CreatedOn datetime,
	@UpdatedOn datetime,
	@MobileNumber	nvarchar(20)
AS
BEGIN
	INSERT INTO [dbo].[Address]
           ([UserID]
           ,[IsBillingAddress]
           ,[FirstName]
           ,[LastName]
           ,[PhoneNumber]
           ,[MobileNumber] 
           ,[Email]
           ,[FaxNumber]
           ,[Company]
           ,[Address1]
           ,[Address2]
           ,[CityID]
           ,[StateProvinceID]
           ,[ZipPostalCode]
           ,[CountryID]
           ,[CreatedOn]
           ,[UpdatedOn])
    VALUES(
			@UserID,
			@IsBillingAddress,
			@FirstName,
			@LastName,
			@PhoneNumber,
			@MobileNumber,
			@Email,
			@FaxNumber,
			@Company,
			@Address1,
			@Address2,
			@CityID,
			@StateProvinceID,
			@ZipPostalCode,
			@CountryID,
			@CreatedOn,
			@UpdatedOn
			)

	RETURN(@@Identity)
END
GO

-- =============================================
-- add category
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_Category_Create' 
)
   DROP PROCEDURE ECommerce_Category_Create
GO

CREATE PROCEDURE ECommerce_Category_Create
	@CategoryName nvarchar(100),
	@Description nvarchar(max),
	@ParentCategoryID int,
	@PictureID int,
	@ShowOnHomePage bit,
	@Published bit,
	@Deleted bit,
	@DisplayOrder int,
	@CreatedOn datetime,
	@UpdatedOn datetime,
	@LanguageID int
AS
BEGIN
	INSERT INTO [dbo].[Category]
           ([CategoryName]
           ,[Description]
           ,[ParentCategoryID]
           ,[PictureID]
           ,[ShowOnHomePage]
           ,[Published]
           ,[Deleted]
           ,[DisplayOrder]
           ,[CreatedOn]
           ,[UpdatedOn]
		   ,[LanguageID]
			)
    VALUES(
			@CategoryName,
			@Description,
			@ParentCategoryID,
			@PictureID,
			@ShowOnHomePage,
			@Published,
			@Deleted,
			@DisplayOrder,
			@CreatedOn,
			@UpdatedOn,
			@LanguageID
			)

	RETURN(@@Identity)
END
GO


-- =============================================
-- add Document
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_Document_Create' 
)
   DROP PROCEDURE ECommerce_Document_Create
GO

CREATE PROCEDURE ECommerce_Document_Create
	@DocumentBinary varbinary(max),
    @Extension nvarchar(50)
AS
BEGIN
	INSERT INTO [dbo].[Document]
           ([DocumentBinary]
           ,[Extension]
			)
    VALUES(
			@DocumentBinary,
			@Extension
			)

	RETURN(@@Identity)
END
GO


-- =============================================
-- add LocalStringResource
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_LocalStringResource_Create' 
)
   DROP PROCEDURE ECommerce_LocalStringResource_Create
GO

CREATE PROCEDURE ECommerce_LocalStringResource_Create
	@LanguageID int,
    @ResourceName nvarchar(200),
    @ResourceValue nvarchar(max)
AS
BEGIN
	INSERT INTO [dbo].[LocalStringResource]
           ([LanguageID]
           ,[ResourceName]
           ,[ResourceValue]
			)
    VALUES(
			@LanguageID,
			@ResourceName,
			@ResourceValue
			)

	RETURN(@@Identity)
END
GO

-- =============================================
-- add News
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_News_Create' 
)
   DROP PROCEDURE ECommerce_News_Create
GO

CREATE PROCEDURE ECommerce_News_Create
	@LanguageID int,
	@Title nvarchar(80),
	@ShortContent nvarchar(400),
	@FullContent nvarchar(max),
	@Published bit,
	@AllowComments bit,
	@CreatedOn datetime
AS
BEGIN
	INSERT INTO [dbo].[News]
           ([LanguageID]
           ,[Title]
           ,[ShortContent]
           ,[FullContent]
           ,[Published]
           ,[AllowComments]
           ,[CreatedOn]
			)
    VALUES(
			@LanguageID,
			@Title,
			@ShortContent,
			@FullContent,
			@Published,
			@AllowComments,
			@CreatedOn
			)

	RETURN(@@Identity)
END
GO


-- =============================================
-- add Picture
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_Picture_Create' 
)
   DROP PROCEDURE ECommerce_Picture_Create
GO

CREATE PROCEDURE ECommerce_Picture_Create
	@PictureBinary varbinary(max),
    @Extension nvarchar(20),
    @IsNew bit
AS
BEGIN
	INSERT INTO [dbo].[Picture]
           ([PictureBinary]
           ,[Extension]
           ,[IsNew]
			)
    VALUES(
			@PictureBinary,
			@Extension,
			@IsNew
			)

	RETURN(@@Identity)
END
GO

-- =============================================
-- add Product
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_Product_Create' 
)
   DROP PROCEDURE ECommerce_Product_Create
GO

CREATE PROCEDURE ECommerce_Product_Create
	@Name nvarchar(100),
    @ShortDescription nvarchar(300),
    @FullDescription nvarchar(max),
    @ShowOnHomePage bit,
    @MetaKeywords nvarchar(400),
    @MetaTitle nvarchar(100),
    @AllowUserReviews bit,
    @AllowUserRatings bit,
    @RatingSum int,
    @TotalRatingVotes int,
    @Published bit,
    @Deleted bit,
    @CreatedOn datetime,
    @UpdatedOn datetime,
    @Cost money,
    @Size nvarchar(50),
    @SizeUnit nvarchar(20),
    @Weight decimal(8,2),
    @WeightUnit nvarchar(20),
    @ListPrice money,
    @Quantity int,
    @Recommend bit,
	@LanguageID	int,
	@ProductCode nvarchar(20),
	@CashTicketLimit money,
	@DisplayOrder	int
AS
BEGIN
	INSERT INTO [dbo].[Product]
           ([Name]
           ,[ShortDescription]
           ,[FullDescription]
           ,[ShowOnHomePage]
           ,[MetaKeywords]
           ,[MetaTitle]
           ,[AllowUserReviews]
           ,[AllowUserRatings]
           ,[RatingSum]
           ,[TotalRatingVotes]
           ,[Published]
           ,[Deleted]
           ,[CreatedOn]
           ,[UpdatedOn]
           ,[Cost]
           ,[Size]
           ,[SizeUnit]
           ,[Weight]
           ,[WeightUnit]
           ,[ListPrice]
           ,[Quantity]
           ,[Recommend]
		   ,[LanguageID]
		   ,[ProductCode]
		   ,[CashTicketLimit]
		   ,[DisplayOrder]
			)
    VALUES(
			@Name,
			@ShortDescription,
			@FullDescription,
			@ShowOnHomePage,
			@MetaKeywords,
			@MetaTitle,
			@AllowUserReviews,
			@AllowUserRatings,
			@RatingSum,
			@TotalRatingVotes,
			@Published,
			@Deleted,
			@CreatedOn,
			@UpdatedOn,
			@Cost,
			@Size,
			@SizeUnit,
			@Weight,
			@WeightUnit,
			@ListPrice,
			@Quantity,
			@Recommend,
			@LanguageID,
			@ProductCode,
			@CashTicketLimit,
			@DisplayOrder
			)

	RETURN(@@Identity)
END
GO

-- =============================================
-- add ProductAdditionalProperty
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_ProductAddtnlProperty_Create' 
)
   DROP PROCEDURE ECommerce_ProductAddtnlProperty_Create
GO

CREATE PROCEDURE ECommerce_ProductAddtnlProperty_Create
	@ProductID int,
    @PropertyName nvarchar(50),
    @PropertyValue nvarchar(max),
	@DisplayOrder	int
AS
BEGIN
	INSERT INTO [dbo].[ProductAdditionalProperty]
           ([ProductID]
           ,[PropertyName]
           ,[PropertyValue]
		   ,[DisplayOrder]
			)
    VALUES(
			@ProductID,
			@PropertyName,
			@PropertyValue,
			@DisplayOrder
			)

	RETURN(@@Identity)
END
GO


-- =============================================
-- add ProductPicture
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_ProductPicture_Create' 
)
   DROP PROCEDURE ECommerce_ProductPicture_Create
GO

CREATE PROCEDURE ECommerce_ProductPicture_Create
	@ProductID int,
    @PictureID int,
    @DisplayOrder int
AS
BEGIN
	INSERT INTO [dbo].[ProductPicture]
           ([ProductID]
           ,[PictureID]
           ,[DisplayOrder]
			)
    VALUES(
			@ProductID,
			@PictureID,
			@DisplayOrder
			)

	RETURN(@@Identity)
END
GO


-- =============================================
-- add ProductRating
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_ProductRating_Create' 
)
   DROP PROCEDURE ECommerce_ProductRating_Create
GO

CREATE PROCEDURE ECommerce_ProductRating_Create
	@ProductID int,
    @UserID uniqueidentifier,
    @Rating int,
    @RatedOn datetime
AS
BEGIN
	INSERT INTO [dbo].[ProductRating]
           ([ProductID]
           ,[UserID]
           ,[Rating]
           ,[RatedOn]
			)
    VALUES(
			@ProductID,
			@UserID,
			@Rating,
			@RatedOn
			)

	RETURN(@@Identity)
END
GO


-- =============================================
-- add ProductReview
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_ProductReview_Create' 
)
   DROP PROCEDURE ECommerce_ProductReview_Create
GO

CREATE PROCEDURE ECommerce_ProductReview_Create
	@ProductID int,
    @UserID uniqueidentifier,
    @IPAddress nvarchar(100),
    @Title nvarchar(1000),
    @ReviewText nvarchar(max),
    @Rating int,
    @HelpfulYesTotal int,
    @HelpfulNoTotal int,
    @IsApproved bit,
    @CreatedOn datetime
AS
BEGIN
	INSERT INTO [dbo].[ProductReview]
           ([ProductID]
           ,[UserID]
           ,[IPAddress]
           ,[Title]
           ,[ReviewText]
           ,[Rating]
           ,[HelpfulYesTotal]
           ,[HelpfulNoTotal]
           ,[IsApproved]
           ,[CreatedOn]
			)
    VALUES(
			@ProductID,
			@UserID,
			@IPAddress,
			@Title,
			@ReviewText,
			@Rating,
			@HelpfulYesTotal,
			@HelpfulNoTotal,
			@IsApproved,
			@CreatedOn
			)

	RETURN(@@Identity)
END
GO


-- =============================================
-- add ProductReviewHelpfulness
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_ProductRvwHlpflnss_Create' 
)
   DROP PROCEDURE ECommerce_ProductRvwHlpflnss_Create
GO

CREATE PROCEDURE ECommerce_ProductRvwHlpflnss_Create
	@ProductReviewID int,
    @UserID uniqueidentifier,
    @WasHelpful bit
AS
BEGIN
	INSERT INTO [dbo].[ProductReviewHelpfulness]
           ([ProductReviewID]
           ,[UserID]
           ,[WasHelpful]
			)
    VALUES(
			@ProductReviewID,
			@UserID,
			@WasHelpful
			)

	RETURN(@@Identity)
END
GO


-- =============================================
-- add ProductTag
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_ProductTag_Create' 
)
   DROP PROCEDURE ECommerce_ProductTag_Create
GO

CREATE PROCEDURE ECommerce_ProductTag_Create
	@Name nvarchar(100),
	@LanguageID int
AS
BEGIN
	INSERT INTO [dbo].[ProductTag]
           ([Name]
           ,[ProductCount]
		   ,[LanguageID]
			)
    VALUES(
			@Name,
			0,
			@LanguageID
			)

	RETURN(@@Identity)
END
GO


-- =============================================
-- add ProductTagProductMapping
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_ProductTagProductMapping_Create' 
)
   DROP PROCEDURE ECommerce_ProductTagProductMapping_Create
GO

CREATE PROCEDURE ECommerce_ProductTagProductMapping_Create
	@ProductTagID int,
    @ProductID int
AS
BEGIN
	INSERT INTO [dbo].[ProductTag_Product_Mapping]
           ([ProductTagID],
            [ProductID]
			)
    VALUES(
			@ProductTagID,
			@ProductID
			)
			
	UPDATE [dbo].[ProductTag]
	SET ProductCount = ProductCount + 1
	WHERE ProductTagID = @ProductTagID

	RETURN(@@Identity)
END
GO


-- =============================================
-- add Product_Category_Mapping
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_ProductCategoryMapping_Create' 
)
   DROP PROCEDURE ECommerce_ProductCategoryMapping_Create
GO

CREATE PROCEDURE ECommerce_ProductCategoryMapping_Create
	@ProductID int,
    @CategoryID int,
    @IsFeaturedProduct bit,
    @DisplayOrder int
AS
BEGIN
	INSERT INTO [dbo].[Product_Category_Mapping]
           ([ProductID]
           ,[CategoryID]
           ,[IsFeaturedProduct]
           ,[DisplayOrder]
			)
    VALUES(
			@ProductID,
			@CategoryID,
			@IsFeaturedProduct,
			@DisplayOrder
			)

	RETURN(@@Identity)
END
GO


-- =============================================
-- add Product_Document_Mapping
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_ProductDocumentMapping_Create' 
)
   DROP PROCEDURE ECommerce_ProductDocumentMapping_Create
GO

CREATE PROCEDURE ECommerce_ProductDocumentMapping_Create
	@DocumentID int,
    @ProductID int,
    @DisplayOrder int
AS
BEGIN
	INSERT INTO [dbo].[Product_Document_Mapping]
           ([DocumentID]
           ,[ProductID]
           ,[DisplayOrder]
			)
    VALUES(
			@DocumentID,
			@ProductID,
			@DisplayOrder
			)

	RETURN(@@Identity)
END
GO


-- =============================================
-- add RelatedProduct
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_RelatedProduct_Create' 
)
   DROP PROCEDURE ECommerce_RelatedProduct_Create
GO

CREATE PROCEDURE ECommerce_RelatedProduct_Create
	@ProductID1 int,
    @ProductID2 int,
    @DisplayOrder int
AS
BEGIN
	IF NOT EXISTS(SELECT * FROM [dbo].[RelatedProduct] WHERE [ProductID1]=@ProductID1 AND [ProductID2]=@ProductID2)
	BEGIN
		INSERT INTO [dbo].[RelatedProduct]
			   ([ProductID1]
			   ,[ProductID2]
			   ,[DisplayOrder]
				)
		VALUES(
				@ProductID1,
				@ProductID2,
				@DisplayOrder
				)

		RETURN(@@Identity)
	END
	ELSE
	BEGIN
		RETURN(0)
	END
END
GO


-- =============================================
-- add UserExtension
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_UserExtension_Create' 
)
   DROP PROCEDURE ECommerce_UserExtension_Create
GO

CREATE PROCEDURE ECommerce_UserExtension_Create
	@UserID uniqueidentifier,
    @AffiliateID int,
    @BillingAddressID int,
    @ShippingAddressID int,
    @LastPaymentMethodID int,
    @LastAppliedCouponCode nvarchar(100),
    @LanguageID int,
    @TotalForumPosts int,
    @Signature nvarchar(300),
    @Active bit,
    @Deleted bit,
    @RegistrationDate datetime,
    @CurrencyID int,
    @FirstName nvarchar(50),
    @LastName nvarchar(50),
    @PhoneNumber nvarchar(20),
    @MobileNumber nvarchar(20)
AS
BEGIN
	INSERT INTO [dbo].[UserExtension]
           ([UserID]
           ,[AffiliateID]
           ,[BillingAddressID]
           ,[ShippingAddressID]
           ,[LastPaymentMethodID]
           ,[LastAppliedCouponCode]
           ,[LanguageID]
           ,[TotalForumPosts]
           ,[Signature]
           ,[Active]
           ,[Deleted]
           ,[RegistrationDate]
           ,[CurrencyID]
           ,[FirstName] 
           ,[LastName]
           ,[PhoneNumber] 
           ,[MobileNumber] 
			)
    VALUES(
			@UserID,
			@AffiliateID,
			@BillingAddressID,
			@ShippingAddressID,
			@LastPaymentMethodID,
			@LastAppliedCouponCode,
			@LanguageID,
			@TotalForumPosts,
			@Signature,
			@Active,
			@Deleted,
			@RegistrationDate,
			@CurrencyID,
			@FirstName,
			@LastName,
			@PhoneNumber,
			@MobileNumber
			)

	RETURN(1)
END
GO


-- =============================================
-- add UserSession
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_UserSession_Create' 
)
   DROP PROCEDURE ECommerce_UserSession_Create
GO

CREATE PROCEDURE ECommerce_UserSession_Create
	@UserSessionGUID uniqueidentifier, 
    @UserID uniqueidentifier, 
    @LastAccessed datetime, 
    @IsExpired bit
AS
BEGIN
	INSERT INTO [dbo].[UserSession]
           ([UserSessionGUID]
           ,[UserID]
           ,[LastAccessed]
           ,[IsExpired]
			)
    VALUES(
			@UserSessionGUID, 
			@UserID, 
			@LastAccessed, 
			@IsExpired
			)

	RETURN(@@Identity)
END
GO

-- =============================================
-- add ShoppingCartItem
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_ShoppingCartItem_Create' 
)
   DROP PROCEDURE ECommerce_ShoppingCartItem_Create
GO

CREATE PROCEDURE ECommerce_ShoppingCartItem_Create
	@ShoppingCartTypeID int,
    @UserID uniqueidentifier,
    @ProductID int,
    @AttributesXML xml,
    @UserEnteredPrice money,
    @Quantity int,
    @CreatedOn datetime,
    @UpdatedOn datetime
AS
BEGIN
	IF NOT EXISTS (
		SELECT ShoppingCartItemID FROM [dbo].[ShoppingCartItem] 
		WHERE ProductID=@ProductID AND UserID=@UserID
	)
	BEGIN
		INSERT INTO [dbo].[ShoppingCartItem]
			   ([ShoppingCartTypeID]
			   ,[UserID]
			   ,[ProductID]
			   ,[AttributesXML]
			   ,[UserEnteredPrice]
			   ,[Quantity]
			   ,[CreatedOn]
			   ,[UpdatedOn])
		VALUES(
				@ShoppingCartTypeID,
				@UserID,
				@ProductID,
				@AttributesXML,
				@UserEnteredPrice,
				@Quantity,
				@CreatedOn,
				@UpdatedOn
				)

		RETURN(@@Identity)
	END
END
GO


-- =============================================
-- add ShippingMethod
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_ShippingMethod_Create' 
)
   DROP PROCEDURE ECommerce_ShippingMethod_Create
GO

CREATE PROCEDURE ECommerce_ShippingMethod_Create
	@Name nvarchar(100),
    @Description nvarchar(2000),
    @DisplayOrder int,
    @IsActive bit
AS
BEGIN
	INSERT INTO [dbo].[ShippingMethod]
           ([Name]
           ,[Description]
           ,[DisplayOrder]
           ,[IsActive])
    VALUES(
			@Name,
			@Description,
			@DisplayOrder,
			@IsActive
			)

	RETURN(@@Identity)
END
GO


-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_PaymentMethod_Create' 
)
   DROP PROCEDURE ECommerce_PaymentMethod_Create
GO

CREATE PROCEDURE ECommerce_PaymentMethod_Create
	@Name nvarchar(100),
    @VisibleName nvarchar(100),
    @Description nvarchar(4000),
    @ConfigureTemplatePath nvarchar(500),
    @UserTemplatePath nvarchar(500),
    @ClassName nvarchar(500),
    @SystemKeyword nvarchar(500),
    @IsActive bit,
    @DisplayOrder int
AS
BEGIN
	INSERT INTO [dbo].[PaymentMethod]
           ([Name]
           ,[VisibleName]
           ,[Description]
           ,[ConfigureTemplatePath]
           ,[UserTemplatePath]
           ,[ClassName]
           ,[SystemKeyword]
           ,[IsActive]
           ,[DisplayOrder])
    VALUES(
			@Name,
			@VisibleName,
			@Description,
			@ConfigureTemplatePath,
			@UserTemplatePath,
			@ClassName,
			@SystemKeyword,
			@IsActive,
			@DisplayOrder
			)

	RETURN(@@Identity)
END
GO


-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_Express_Create' 
)
   DROP PROCEDURE ECommerce_Express_Create
GO

CREATE PROCEDURE ECommerce_Express_Create
	@ExpressID int,
    @ExpressName nvarchar(50),
    @ExpressDescription nvarchar(300),
    @IsActive bit,
    @PhoneNumber nvarchar(50),
    @Email nvarchar(50),
    @FaxNumber nvarchar(50),
    @Address1 nvarchar(150),
    @Address2 nvarchar(150),
    @CityID int,
    @StateProvinceID int,
    @CountryID int
AS
BEGIN
	INSERT INTO [dbo].[Express]
           ([ExpressID]
           ,[ExpressName]
           ,[ExpressDescription]
           ,[IsActive]
           ,[PhoneNumber]
           ,[Email]
           ,[FaxNumber]
           ,[Address1]
           ,[Address2]
           ,[CityID]
           ,[StateProvinceID]
           ,[CountryID])
    VALUES(
			@ExpressID,
			@ExpressName,
			@ExpressDescription,
			@IsActive,
			@PhoneNumber,
			@Email,
			@FaxNumber,
			@Address1,
			@Address2,
			@CityID,
			@StateProvinceID,
			@CountryID
			)

	RETURN(@@Identity)
END
GO


-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_OrderNote_Create' 
)
   DROP PROCEDURE ECommerce_OrderNote_Create
GO

CREATE PROCEDURE ECommerce_OrderNote_Create
	@OrderID int,
    @Note nvarchar(4000),
    @DisplayToUser bit,
    @CreatedOn datetime
AS
BEGIN
	INSERT INTO [dbo].[OrderNote]
           ([OrderID]
           ,[Note]
           ,[DisplayToUser]
           ,[CreatedOn])
    VALUES(
			@OrderID,
			@Note,
			@DisplayToUser,
			@CreatedOn
			)

	RETURN(@@Identity)
END
GO


-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_Order_Create' 
)
   DROP PROCEDURE ECommerce_Order_Create
GO

CREATE PROCEDURE ECommerce_Order_Create
	--@OrderGUID uniqueidentifier,
    @UserID uniqueidentifier,
    @UserLanguageID int,
    @UserTaxDisplayTypeID int,
    @UserIP nvarchar(50),
    @OrderSubtotalInclTax money,
    @OrderSubtotalExclTax money,
    @OrderShippingInclTax money,
    @OrderShippingExclTax money,
    @PaymentMethodAdditionalFeeInclTax money,
    @PaymentMethodAdditionalFeeExclTax money,
    @OrderTax money,
    @OrderTotal money,
    @OrderDiscount money,
    @OrderSubtotalInclTaxInUserCurrency money,
    @OrderSubtotalExclTaxInUserCurrency money,
    @OrderShippingInclTaxInUserCurrency money,
    @OrderShippingExclTaxInUserCurrency money,
    @PaymentMethodAdditionalFeeInclTaxInUserCurrency money,
    @PaymentMethodAdditionalFeeExclTaxInUserCurrency money,
    @OrderTaxInUserCurrency money,
    @OrderTotalInUserCurrency money,
    @OrderDiscountInUserCurrency money,
    @UserCurrencyCode nvarchar(5),
    @CheckoutAttributeDescription nvarchar(4000),
    @CheckoutAttributesXML xml,
    @OrderWeight float,
    @CashTicketId int,
    @OrderStatusID int,
    @AllowStoringCreditCardNumber bit,
    @CardType nvarchar(100),
    @CardName nvarchar(100),
    @CardNumber nvarchar(100),
    @MaskedCreditCardNumber nvarchar(100),
    @CardCVV2 nvarchar(100),
    @CardExpirationMonth nvarchar(100),
    @CardExpirationYear nvarchar(100),
    @PaymentMethodID int,
    @PaymentMethodName nvarchar(100),
    @AuthorizationTransactionID nvarchar(4000),
    @AuthorizationTransactionCode nvarchar(4000),
    @AuthorizationTransactionResult nvarchar(4000),
    @CaptureTransactionID nvarchar(4000),
    @CaptureTransactionResult nvarchar(4000),
    @SubscriptionTransactionID nvarchar(4000),
    @PurchaseOrderNumber nvarchar(100),
    @PaymentStatusID int,
    @PaidDate datetime,
    @ShippingStatusID int,
    @ShippingFirstName nvarchar(100),
    @ShippingLastName nvarchar(100),
    @ShippingPhoneNumber nvarchar(50),
    @ShippingEmail nvarchar(255),
    @ShippingFaxNumber nvarchar(50),
    @ShippingCompany nvarchar(100),
    @ShippingAddress1 nvarchar(100),
    @ShippingAddress2 nvarchar(100),
    @ShippingCity nvarchar(100),
    @ShippingStateProvince nvarchar(100),
    @ShippingStateProvinceID int,
    @ShippingZipPostalCode nvarchar(10),
    @ShippingCountry nvarchar(100),
    @ShippingCountryID int,
	@ShippingMethodID int,
    @ShippingMethod nvarchar(100),
    @ShippedDate datetime,
	@ShippingMobileNumber nvarchar(20),
    @TrackingNumber nvarchar(100),
    @DeliveryDate datetime,
    @Deleted bit,
    @CreatedOn datetime,
	@ShippingCityID int,
	@Remark	nvarchar(300),
	@InvoicingHeader nvarchar(50),
	@InvoicingContent nvarchar(200),
	@DiscountLimit money
AS
BEGIN

	DECLARE @TheNewOrderNumber nvarchar(13)

	IF exists(SELECT OrderNumber from [dbo].[Order] where left(OrderNumber,8) = convert(varchar(8),getdate(),112))
	BEGIN
		SELECT TOP 1 @TheNewOrderNumber = left(OrderNumber,8) + right('0000' + cast((cast(right(OrderNumber,4) as int)) + 1 AS varchar(4)),4)  
		FROM [dbo].[Order] WHERE left(OrderNumber,8) = convert(varchar(8),getdate(),112) ORDER BY OrderNumber DESC
	END
	ELSE
	BEGIN
		SELECT @TheNewOrderNumber = convert(varchar(8),getdate(),112)+'0001'
	END

	INSERT INTO [dbo].[Order]
           ([UserID]
           ,[UserLanguageID]
           ,[UserTaxDisplayTypeID]
           ,[UserIP]
           ,[OrderSubtotalInclTax]
           ,[OrderSubtotalExclTax]
           ,[OrderShippingInclTax]
           ,[OrderShippingExclTax]
           ,[PaymentMethodAdditionalFeeInclTax]
           ,[PaymentMethodAdditionalFeeExclTax]
           ,[OrderTax]
           ,[OrderTotal]
           ,[OrderDiscount]
           ,[OrderSubtotalInclTaxInUserCurrency]
           ,[OrderSubtotalExclTaxInUserCurrency]
           ,[OrderShippingInclTaxInUserCurrency]
           ,[OrderShippingExclTaxInUserCurrency]
           ,[PaymentMethodAdditionalFeeInclTaxInUserCurrency]
           ,[PaymentMethodAdditionalFeeExclTaxInUserCurrency]
           ,[OrderTaxInUserCurrency]
           ,[OrderTotalInUserCurrency]
           ,[OrderDiscountInUserCurrency]
           ,[UserCurrencyCode]
           ,[CheckoutAttributeDescription]
           ,[CheckoutAttributesXML]
           ,[OrderWeight]
           ,[CashTicketId]
           ,[OrderStatusID]
           ,[AllowStoringCreditCardNumber]
           ,[CardType]
           ,[CardName]
           ,[CardNumber]
           ,[MaskedCreditCardNumber]
           ,[CardCVV2]
           ,[CardExpirationMonth]
           ,[CardExpirationYear]
           ,[PaymentMethodID]
           ,[PaymentMethodName]
           ,[AuthorizationTransactionID]
           ,[AuthorizationTransactionCode]
           ,[AuthorizationTransactionResult]
           ,[CaptureTransactionID]
           ,[CaptureTransactionResult]
           ,[SubscriptionTransactionID]
           ,[PurchaseOrderNumber]
           ,[PaymentStatusID]
           ,[PaidDate]
           ,[ShippingStatusID]
           ,[ShippingFirstName]
           ,[ShippingLastName]
           ,[ShippingPhoneNumber]
           ,[ShippingEmail]
           ,[ShippingFaxNumber]
           ,[ShippingCompany]
           ,[ShippingAddress1]
           ,[ShippingAddress2]
           ,[ShippingCity]
           ,[ShippingStateProvince]
           ,[ShippingStateProvinceID]
           ,[ShippingZipPostalCode]
           ,[ShippingCountry]
           ,[ShippingCountryID]
           ,[ShippingMethod]
           ,[ShippingMethodID]
           ,[ShippedDate]
		   ,[ShippingMobileNumber]
           ,[TrackingNumber]
           ,[DeliveryDate]
           ,[Deleted]
           ,[CreatedOn]
		   ,[ShippingCityID]
		   ,[OrderNumber]
		   ,[Remark]
		   ,[InvoicingHeader]
		   ,[InvoicingContent]
		   ,[DiscountLimit])
    VALUES(
--			@OrderGUID,
			@UserID,
			@UserLanguageID,
			@UserTaxDisplayTypeID,
			@UserIP,
			@OrderSubtotalInclTax,
			@OrderSubtotalExclTax,
			@OrderShippingInclTax,
			@OrderShippingExclTax,
			@PaymentMethodAdditionalFeeInclTax,
			@PaymentMethodAdditionalFeeExclTax,
			@OrderTax,
			@OrderTotal,
			@OrderDiscount,
			@OrderSubtotalInclTaxInUserCurrency,
			@OrderSubtotalExclTaxInUserCurrency,
			@OrderShippingInclTaxInUserCurrency,
			@OrderShippingExclTaxInUserCurrency,
			@PaymentMethodAdditionalFeeInclTaxInUserCurrency,
			@PaymentMethodAdditionalFeeExclTaxInUserCurrency,
			@OrderTaxInUserCurrency,
			@OrderTotalInUserCurrency,
			@OrderDiscountInUserCurrency,
			@UserCurrencyCode,
			@CheckoutAttributeDescription,
			@CheckoutAttributesXML,
			@OrderWeight,
			@CashTicketId,
			@OrderStatusID,
			@AllowStoringCreditCardNumber,
			@CardType,
			@CardName,
			@CardNumber,
			@MaskedCreditCardNumber,
			@CardCVV2,
			@CardExpirationMonth,
			@CardExpirationYear,
			@PaymentMethodID,
			@PaymentMethodName,
			@AuthorizationTransactionID,
			@AuthorizationTransactionCode,
			@AuthorizationTransactionResult,
			@CaptureTransactionID,
			@CaptureTransactionResult,
			@SubscriptionTransactionID,
			@PurchaseOrderNumber,
			@PaymentStatusID,
			@PaidDate,
			@ShippingStatusID,
			@ShippingFirstName,
			@ShippingLastName,
			@ShippingPhoneNumber,
			@ShippingEmail,
			@ShippingFaxNumber,
			@ShippingCompany,
			@ShippingAddress1,
			@ShippingAddress2,
			@ShippingCity,
			@ShippingStateProvince,
			@ShippingStateProvinceID,
			@ShippingZipPostalCode,
			@ShippingCountry,
			@ShippingCountryID,
			@ShippingMethod,
			@ShippingMethodID,
			@ShippedDate,
			@ShippingMobileNumber,
			@TrackingNumber,
			@DeliveryDate,
			@Deleted,
			@CreatedOn,
			@ShippingCityID,
			@TheNewOrderNumber,
			@Remark,
			@InvoicingHeader,
			@InvoicingContent,
			@DiscountLimit
			)

	RETURN(@@Identity)
END
GO

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_Language_Create' 
)
   DROP PROCEDURE ECommerce_Language_Create
GO

CREATE PROCEDURE ECommerce_Language_Create
	@Name nvarchar(100),
	@LanguageCulture nvarchar(20),
	@FlagImageFileName nvarchar(50),
	@Published bit,
	@DisplayOrder int
AS
BEGIN
	INSERT INTO [dbo].[Language]
           ([Name]
           ,[LanguageCulture]
           ,[FlagImageFileName]
           ,[Published]
           ,[DisplayOrder])
     VALUES
           (@Name,
           @LanguageCulture,
           @FlagImageFileName,
           @Published,
           @DisplayOrder)

	RETURN(@@Identity)
END
GO


-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_Order_Product_Mapping_Create' 
)
   DROP PROCEDURE ECommerce_Order_Product_Mapping_Create
GO

CREATE PROCEDURE ECommerce_Order_Product_Mapping_Create
	@OrderID int,
	@ProductID int,
	@Quantity int,
	@CreatedOn datetime,
	@UpdatedOn datetime
AS
BEGIN
	INSERT INTO [dbo].[Order_Product_Mapping]
           ([OrderID]
           ,[ProductID]
           ,[Quantity]
           ,[CreatedOn]
           ,[UpdatedOn])
     VALUES
           (@OrderID
           ,@ProductID
           ,@Quantity
           ,@CreatedOn
           ,@UpdatedOn)

	RETURN(@@Identity)
END
GO

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME =  N'ECommerce_CashTicket_Create' 
)
   DROP PROCEDURE ECommerce_CashTicket_Create
GO

CREATE PROCEDURE ECommerce_CashTicket_Create
	@Cost money,
	@ExpiredDate datetime,
	@CreatedOn datetime,
	@IsUsed int,
	@UserID uniqueidentifier
AS
BEGIN
	DECLARE @TheNewCashTicketNumber nvarchar(13)

	IF exists(SELECT CashTicketNumber from [dbo].[CashTicket] where left(CashTicketNumber,8) = convert(varchar(8),getdate(),112))
	BEGIN
		SELECT TOP 1 @TheNewCashTicketNumber = left(CashTicketNumber,8) + right('0000' + cast((cast(right(CashTicketNumber,4) as int)) + 1 AS varchar(4)),4)  
		FROM [dbo].[CashTicket] WHERE left(CashTicketNumber,8) = convert(varchar(8),getdate(),112) ORDER BY CashTicketNumber DESC
	END
	ELSE
	BEGIN
		SELECT @TheNewCashTicketNumber = convert(varchar(8),getdate(),112)+'0001'
	END

INSERT INTO [dbo].[CashTicket]
           ([CashTicketNumber]
           ,[Cost]
           ,[ExpiredDate]
           ,[CreatedOn]
           ,[IsUsed]
           ,[UserID])
     VALUES
           (@TheNewCashTicketNumber
           ,@Cost
           ,@ExpiredDate
           ,@CreatedOn
           ,@IsUsed
           ,@UserID)

	RETURN(@@Identity)
END
GO

